package org.unt.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.Set;

import org.unt.logic.StringLogic;

public class DbCopier {

	public static void main(String[] args) throws Exception {

		Properties properties = StringLogic.load("config.properties");

		Db dbFrom = new Db(properties, "orafrom");
		Db dbTo = new Db(properties, "orato");

		List<Column> cols = dbFrom.getColumnsFromTable("STOPTIMEBO", "NAME");

		copyTable("STOPTIMEBO", dbFrom, dbTo, cols);

		/*
		 * Set<String> processedTables = new HashSet<String>(); List<String> allTables = fetchAllTables(dbFrom);
		 * 
		 * while (allTables.size() > 0) { for (String table : allTables) { List<String> relTables = fetchRelTables(table, dbFrom); if
		 * (allRelTablesDone(relTables, dbFrom, processedTables)) { processedTables.add(table); System.out.println(table); allTables.remove(table); break; } } }
		 */

		dbFrom.close();
	}

	static boolean allRelTablesDone(List<String> relTables, Db db, Set<String> set) {
		for (String table : relTables) {
			if (!set.contains(table))
				return false;
		}
		return true;
	}

	static List<String> fetchAllTables(Db db) throws SQLException {
		return db.selectOneColumnString("select TABLE_NAME from ALL_TABLES where OWNER = 'SQUALEPROD'");
	}

	static List<String> fetchRelTables(String table, Db db) throws SQLException {
		List<String> relTables = db
				.selectOneColumnString("select TABLE_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and R_CONSTRAINT_NAME in ( select CONSTRAINT_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE in ('P','U') and TABLE_NAME = '"
						+ table + "' )");
		return relTables;
	}

	public static void copyTable(String table, Db dbFrom, Db dbTo, List<Column> columns) throws SQLException {

		PreparedStatement prepared = dbTo.buildInsertPrepared(table, columns);

		String namesComma = getColumnsAsCommaSeparated(columns);
		ResultSet rs = dbFrom.select("select " + namesComma + " from " + table);
		while (rs.next()) {

			int i = 1;

			for (Column column : columns) {
				if ("DECIMAL".equals(column.getType())) {
					prepared.setDouble(i, rs.getDouble(i));
				} else if ("INTEGER".equals(column.getType())) {
					prepared.setInt(i, rs.getInt(i));
				} else if ("VARCHAR".equals(column.getType())) {
					prepared.setString(i, rs.getString(i));
				} else if ("TIMESTAMP".equals(column.getType())) {
					prepared.setTimestamp(i, rs.getTimestamp(i));
				} else if ("DATE".equals(column.getType())) {
					prepared.setDate(i, rs.getDate(i));
				}

				i++;
			}

			prepared.execute();
		}

	}

	public static String getValuesAsCommaSeparated(ResultSet rs, List<Column> columns) throws SQLException {

		StringBuffer buf = new StringBuffer();

		for (Column column : columns) {

			if ("DECIMAL".equals(column.getType())) {
				buf.append(rs.getDouble(column.getName()));
			} else if ("INTEGER".equals(column.getType())) {
				buf.append(rs.getInt(column.getName()));
			} else if ("VARCHAR".equals(column.getType())) {
				buf.append("'" + rs.getString(column.getName()) + "'");
			} else if ("TIMESTAMP".equals(column.getType())) {
				buf.append("'" + rs.getTimestamp(column.getName()) + "'");
			} else if ("DATE".equals(column.getType())) {
				buf.append("'" + rs.getDate(column.getName()) + "'");
			}
			buf.append(", ");
		}

		return buf.substring(0, buf.length() - 2);
	}

	public static String getColumnsAsCommaSeparated(List<Column> columns) {
		StringBuffer buf = new StringBuffer();
		for (Column column : columns) {
			buf.append(column.getName() + ", ");
		}
		return buf.substring(0, buf.length() - 2);
	}
}
